--drop table #LIMIT_ORG_2
--drop table #A1
declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])

declare @date_convert date = convert(date, @date_update)
--duongdtt2.data.dbo.han_muc_full

SELECT * , case when ID LIKE'%.0050000%' then 'REV_LIMIT'
when ID LIKE'%.0060000%' then 'NON_REV_LIMIT' end as LIMIT_TYPE
 into #LIMIT_ORG_2
  FROM SERVER74.STAGING.DBO.DLYTB_LIMIT_ORG
  WHERE --BUSINESS_DATE=@date_convert  --THAY NGAY--AND  
  (ID LIKE'%.0050000%' OR ID LIKE '%.0060000%')
  AND LIABILITY_NUMBER IN (SELECT CIF FROM TBL_CUSTOMER)
  
 ----------------------------------------------------------------------------------------------------------

SELECT A.*,B.MID_RATE,

B.MID_RATE* 
(CASE WHEN INTERNAL_AMOUNT LIKE'%B%'THEN REPLACE(INTERNAL_AMOUNT,'B','000000000')
      WHEN INTERNAL_AMOUNT LIKE'%M%'THEN REPLACE(INTERNAL_AMOUNT,'M','000000') 
      ELSE INTERNAL_AMOUNT END)AS INTERNAL_AMOUNT_QD,
B.MID_RATE* 
(CASE WHEN ONLINE_LIMIT LIKE'%B%'THEN REPLACE(ONLINE_LIMIT,'B','000000000')
      WHEN ONLINE_LIMIT LIKE'%M%'THEN REPLACE(ONLINE_LIMIT,'M','000000') 
      ELSE ONLINE_LIMIT END)AS ONLINE_LIMIT_QD,
B.MID_RATE* 
(CASE WHEN TOTAL_OS LIKE'%B%'THEN REPLACE(TOTAL_OS,'B','000000000')
      WHEN TOTAL_OS LIKE'%M%'THEN REPLACE(TOTAL_OS,'M','000000') 
      ELSE TOTAL_OS END)AS TOTAL_OS_QD,
B.MID_RATE* 
(CASE WHEN AVAIL_AMT LIKE'%B%'THEN REPLACE(AVAIL_AMT,'B','000000000')
      WHEN AVAIL_AMT LIKE'%M%'THEN REPLACE(AVAIL_AMT,'M','000000') 
      ELSE AVAIL_AMT END)AS AVAIL_AMT_QD
INTO #A1
FROM #LIMIT_ORG_2 A
LEFT JOIN (SELECT * FROM [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE WHERE SAVE_DATE IN ('20161121')) B
ON A.LIMIT_CURRENCY=B.CODE

delete from TBL_XSELLING_RATE_USING_LIMIT

insert TBL_XSELLING_RATE_USING_LIMIT
SELECT A.BUSINESS_DATE,LIMIT_TYPE,LIABILITY_NUMBER AS CIF, B.CUS_NAME, C.BRANCH_NAME_SME, C.REGION, B.BRANCH_ID, B.CUS_OPEN_DATE, B.DAO, B.DAO_NAME, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN
,SUM(ISNULL(ONLINE_LIMIT_QD,0)) AS ONLINE_LIMIT_QD
,SUM(ISNULL(TOTAL_OS_QD,0)) AS TOTAL_OS_QD
,SUM(ISNULL(AVAIL_AMT_QD,0)) AS AVAIL_AMT_QD
--,(SUM(ISNULL(ONLINE_LIMIT_QD,0))/1E9)/(SUM(ISNULL(AVAIL_AMT_QD,0))/1E9) AS RATE 
 FROM #A1 A, TBL_CUSTOMER B, TBL_BRANCH C
 WHERE A.LIABILITY_NUMBER = B.CIF AND B.BRANCH_ID=C.BRANCH_ID AND ONLINE_LIMIT_QD > 0
 GROUP BY A.BUSINESS_DATE,LIMIT_TYPE,LIABILITY_NUMBER, B.CUS_NAME, C.BRANCH_NAME_SME, C.REGION, B.BRANCH_ID, B.CUS_OPEN_DATE, B.DAO, B.DAO_NAME, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN
 ORDER BY A.BUSINESS_DATE,LIMIT_TYPE,LIABILITY_NUMBER

 --drop table TBL_XSELLING_RATE_USING_LIMIT

 delete from TBL_XSELLING_RATE_USING_LIMIT_RAW

insert TBL_XSELLING_RATE_USING_LIMIT_RAW
 SELECT A.BUSINESS_DATE, A.ID,LIMIT_TYPE,LIABILITY_NUMBER AS CIF, B.CUS_NAME, C.BRANCH_NAME_SME, C.REGION, B.BRANCH_ID, B.CUS_OPEN_DATE, B.DAO, B.DAO_NAME, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, A.APPROVAL_DATE, A.ONLINE_LIMIT_DATE, A.EXPIRY_DATE
,ISNULL(INTERNAL_AMOUNT_QD,0) AS INTERNAL_AMOUNT_QD
,ISNULL(ONLINE_LIMIT_QD,0) ONLINE_LIMIT_QD
,ISNULL(TOTAL_OS_QD,0)TOTAL_OS_QD
,ISNULL(AVAIL_AMT_QD,0)AVAIL_AMT_QD
--,(SUM(ISNULL(ONLINE_LIMIT_QD,0))/1E9)/(SUM(ISNULL(AVAIL_AMT_QD,0))/1E9) AS RATE 
 FROM #A1 A, TBL_CUSTOMER B, TBL_BRANCH C
 WHERE A.LIABILITY_NUMBER = B.CIF AND B.BRANCH_ID=C.BRANCH_ID AND ONLINE_LIMIT_QD > 0 and AVAIL_AMT_QD >0
 ORDER BY A.BUSINESS_DATE,LIMIT_TYPE,LIABILITY_NUMBER

 --select * from #tam where AVAIL_AMT_QD < -0.01
 --select * from #A1 order by liability_number

 --select * from TBL_XSELLING_RATE_USING_LIMIT_RAW
 
update b
set b.date_data = a.BUSINESS_DATE
from (select max( business_date) as BUSINESS_DATE from TBL_XSELLING_RATE_USING_LIMIT_RAW) a,  TBL_DATE_BUSINESS b
where b.name_table = 'TBL_XSELLING_RATE_USING_LIMIT'

-- INSERT TBL_DATE_BUSINESS
--select max(BUSINESS_DATE) as date_data, 'TBL_XSELLING_RATE_USING_LIMIT' from TBL_XSELLING_RATE_USING_LIMIT

 --select *  FROM TBL_XSELLING_RATE_USING_LIMIT
 --where AVAIL_AMT_QD < 0
 --order by cif
 ----select distinct liability_number from #A1

 --select * from #A1 order by liability_number

 --select * from TBL_HR where USER_NAME like '%phuongltd%'